﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CPPEI.Coolzon.Core.Repositories.SQL
{
    public class SqlUserRepository : IUserRepository
    {
        public CPPEI.Coolzon.Core.PagedList<Data.User> GetUserList(CPPEI.Coolzon.Core.DbContext context, int start, int limit)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select * from (select *, ROW_NUMBER() OVER(ORDER BY Id) as rownum from [User]) a where a.rownum >= @paramStart and a.rownum <= @paramEnd";
                        cmd.Parameters.Add(new SqlParameter("paramStart", start));
                        cmd.Parameters.Add(new SqlParameter("paramEnd", start + limit));

                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        List<Data.User> userList = null;
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            userList = new List<Data.User>();
                            foreach (DataRow dr in ds.Tables[0].Rows)
                            {
                                Data.User user = new Data.User();
                                user.Id = new Guid(dr["Id"].ToString());
                                user.Account = dr["Account"].ToString();
                                user.Name = dr["Name"].ToString();
                                user.Password = dr["Password"].ToString();
                                user.CreateDate = DateTime.Parse(dr["CreateDate"].ToString());
                                userList.Add(user);
                            }
                            return new PagedList<Data.User>(userList, start, limit, userList.Count);
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool CreateUser(CPPEI.Coolzon.Core.DbContext context, Data.User user)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "insert into [User](Id, Account, Name, Password) values(@paramId, @paramAccount, @paramName, @paramPassword)";
                    cmd.Parameters.Add(new SqlParameter("paramId", user.Id));
                    cmd.Parameters.Add(new SqlParameter("paramAccount", user.Account));
                    cmd.Parameters.Add(new SqlParameter("paramName", user.Name));
                    cmd.Parameters.Add(new SqlParameter("paramPassword", user.Password));

                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool RemoveUser(CPPEI.Coolzon.Core.DbContext context, Data.User user)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "delete from [User] where Id = @paramId";
                    cmd.Parameters.Add(new SqlParameter("paramId", user.Id));
                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool UpdateUser(CPPEI.Coolzon.Core.DbContext context, Data.User user)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "update [User] set Account = @paramAccount, Name = @paramName, Password = @paramPassword where Id = @paramId";
                    cmd.Parameters.Add(new SqlParameter("paramId", user.Id));
                    cmd.Parameters.Add(new SqlParameter("paramAccount", user.Account));
                    cmd.Parameters.Add(new SqlParameter("paramName", user.Name));
                    cmd.Parameters.Add(new SqlParameter("paramPassword", user.Password));
                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 通过userId获取User对象
        /// </summary>
        /// <param name="context"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public Data.User GetUser(CPPEI.Coolzon.Core.DbContext context, Guid userId)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select * from [User] where Id = @paramId";
                        cmd.Parameters.Add(new SqlParameter("paramId", userId));
                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            Data.User user = new Data.User();
                            user.Id = new Guid(ds.Tables[0].Rows[0]["Id"].ToString());
                            user.Account = ds.Tables[0].Rows[0]["Account"].ToString();
                            user.Name = ds.Tables[0].Rows[0]["Name"].ToString();
                            user.Password = ds.Tables[0].Rows[0]["Password"].ToString();
                            user.CreateDate = DateTime.Parse(ds.Tables[0].Rows[0]["CreateDate"].ToString());
                            return user;
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 通过Account用户名获取User对象
        /// </summary>
        /// <param name="context"></param>
        /// <param name="Account"></param>
        /// <returns></returns>
        public Data.User GetUser(DbContext context, string Account)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select * from [User] where Account = @paramAccount";
                        cmd.Parameters.Add(new SqlParameter("paramAccount", Account));
                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            Data.User user = new Data.User();
                            foreach (DataRow dr in ds.Tables[0].Rows)
                            {
                                user.Id = new Guid(dr["Id"].ToString());
                                user.Account = dr["Account"].ToString();
                                user.Name = dr["Name"].ToString();
                                user.Password = dr["Password"].ToString();
                                user.CreateDate = DateTime.Parse(dr["CreateDate"].ToString());
                            }
                            return user;
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
